import re

def convert_ddl(ddl):
    # 定义正则表达式来匹配字段定义
    field_pattern = re.compile(r'(\w+)\s+(\bVARCHAR2\b|\bVARCHAR\b|NUMBER|DATE)\s*(\([^)]*\))?')
    # 定义正则表达式来匹配表名
    table_pattern = re.compile(r'CREATE\s+TABLE\s+(\w+)\s*\((.*?)\)', re.DOTALL | re.IGNORECASE)
    # 定义正则表达式来匹配comment on column语句
    comment_pattern = re.compile(r'COMMENT ON COLUMN (\w+)\.(\w+)\s+IS\s+''(.*)''')

    # 提取表名和字段定义
    match = table_pattern.search(ddl)
    if not match:
        raise ValueError("Invalid DDL format")

    full_table_name = match.group(1)
    fields_str = match.group(2)

    # 分割字段定义
    fields = [field.strip() for field in fields_str.split(',')]
    converted_fields = []
    comments = []

    # 确定schema和table
    if '.' in full_table_name:
        schema, table = full_table_name.split('.', 1)
    else:
        schema = 'public'
        table = full_table_name

    for field in fields:
        field_match = field_pattern.match(field)
        if field_match:
            column_name = field_match.group(1)
            column_type = field_match.group(2)
            column_size = field_match.group(3)  # 可能为空

            if column_type.upper() in ['VARCHAR2', 'VARCHAR']:
                new_field = f'{column_name} text'
            elif column_type.upper() == 'NUMBER':
                new_field = f'{column_name} integer -- todo 待修改'  # 假设所有 NUMBER 类型都转换为 integer
            elif column_type.upper() == 'DATE':
                new_field = f'{column_name} timestamp -- todo 待修改'  # 将 DATE 转换为 timestamp
            else:
                new_field = f'{column_name} {column_type} -- todo 待修改'

            converted_fields.append(new_field)
        else:
            raise ValueError(f"Invalid field format: {field}")

    # 处理comment on column语句
    comments_str = comment_pattern.findall(ddl)
    for comment in comments_str:
        _, column, text = comment
        comments.append(f'COMMENT ON COLUMN {schema}.{table}.{column} IS \'{text}\';')

    # 重新拼接字段定义和comment on column语句
    new_fields_str = ',\n'.join(converted_fields)
    new_ddl = f'CREATE TABLE {full_table_name} (\n{new_fields_str}\n);\n'
    for comment in comments:
        new_ddl += f'{comment}\n'

    return new_ddl

# 原始DDL
original_ddl = """
CREATE TABLE my_table (
    id NUMBER,
    name VARCHAR2(100),
    description VARCHAR(200),
    created_at DATE
);

COMMENT ON COLUMN my_table.name IS 'The name of the person';
COMMENT ON COLUMN my_table.description IS 'A brief description of the person';
"""

# 转换后的DDL
converted_ddl = convert_ddl(original_ddl)
print(converted_ddl.lower())